import time
import sys
sys.path.append('/sw/systems/shared/python/')
import DB
import Global
from function.count_time import count_time


class QubeDatabase:

    def __init__(self, city=Global.default['city']):
        self.cache = {}
        self.city = city
        self.DB = DB.DB()
        self.conn, self.cursor = self.DB.connect('qube', city=self.city)
        self.last_id = self.last_jobid()


    def update(self):
        if self.conn:
            self.conn.close()
        self.conn, self.cursor = self.DB.connect('qube', city=self.city)

    def last_jobid(self):
        self.cursor.execute("select * from qube.jobidmax")
        return self.cursor.fetchone()

    def view(self):
        self.cursor.execute("show tables from qube")
        return self.cursor.fetchall()

    def describe_job(self):
        self.cursor.execute("show columns from qube.job")
        return self.cursor.fetchall()

    def host(self, query_field='*', query=''):
        # local cache
        param = 'host_info_' + query_field + query
        if param in self.cache:
            return self.cache[param]

        sql = "select %s from qube.host where 1=1" % query_field
        if query:
            sql += ' and %s' % query
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.cache[param] = result
        return result

    @count_time
    def job(self, query_field='*', query='', refresh=False):
        # local cache
        param = 'job_info_' + query_field + query
        if not refresh and param in self.cache:
            return self.cache[param]

        if refresh: self.update()

        sql = "select %s from qube.job where 1=1" % query_field
        if query:
            sql += ' and %s' % query
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.cache[param] = result
        return result

    def agenda(self, jobid, query_field='*', query=''):
        try:
            jobid = int(jobid)
        except ValueError:
            raise ValueError('jobid must be integer.')

        # local cache
        param = 'job_agenda_' + str(jobid) + query_field + query
        if param in self.cache: return self.cache[param]

        sql = "select %s from %iqube.%iwork where 1=1" % (query_field, jobid >> 7, jobid)
        if query:
            sql += ' and %s' % query
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        self.cache[param] = result
        return result

    def subjob(self, jobid, query_field='*', query=''):
        try:
            jobid = int(jobid)
        except ValueError:
            raise ValueError('jobid must be integer.')

        sql = "select %s from %iqube.%isubjob where 1=1" % (query_field, jobid >> 7, jobid)
        if query:
            sql += ' and %s' % query
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def callback(self, jobid, query_field='*', query=''):
        try:
            jobid = int(jobid)
        except ValueError:
            raise ValueError('jobid must be integer.')

        sql = "select %s from %iqube.%icallback where 1=1" % (query_field, jobid >> 7, jobid)
        if query:
            sql += ' and %s' % query
        self.cursor.execute(sql)
        return self.cursor.fetchall()

    def subjob_r_count(self):
        # local cache
        job_info = self.job(query_field="id,status,name,cpustally,prod_dept",
                            query="status = 336")
        count = 0
        for i in job_info:
            count += int(i['cpustally'].split(' ')[0])
        return count

    def Katana_subjob_r_count(self):
        # local cache
        job_info = self.job(query_field="id,status,name,cpustally,prod_dept", query="(status = 336) and (name like 'Katana%' or name like 'Denoise%')")
        count = 0
        for i in job_info:
            count += int(i['cpustally'].split(' ')[0])
        return count

    def MR_subjob_r_count(self):
        # local cache
        job_info = self.job(query_field="id,status,name,cpustally,prod_dept",
                            query="(status = 336) and (name like 'MR%')")
        count = 0
        for i in job_info:
            count += int(i['cpustally'].split(' ')[0])
        return count

    def Bj_subjob_count(self):
        # local cache

        def getcity(username):
            import Company
            CompanyUserInfo = Company.Company()
            people_dict = CompanyUserInfo.people()
            if username not in people_dict:
                print 'no this guys : %s '%username
                User_city = 'XM'
                return User_city
            else:
                User_city = people_dict[username]['city']
            return User_city

        job_info = self.job(query_field="id,status,name,cpustally,prod_dept,priority,user,todotally",
                            query="status = 336 or status = 320")
        count_r = 0
        count_p = 0
        for i in job_info:
            username = i['user']
            if getcity(username) != 'XM':
                count_r += int(i['cpustally'].split(' ')[0])
                count_p += int(i['todotally'].split(' ')[3])
            else:
                count_r += 0
                count_p += 0
        print count_r,count_p
        return count_r,count_p

    def pending_job(self):
        job_info = self.job(query_field="todotally",
                            query="(status = 336) or (status = 320)")
        count = 0
        for i in job_info:
            count += int(i['todotally'].split(' ')[3])
        return count

    def get_user_job_name(self,user):
        job_name_list = self.job(query_field='name',query='user="%s"'%user)
        for job_name in job_name_list:
            print job_name['name']



    def close(self):
        self.cursor.close()
        self.conn.close()


if __name__ == '__main__':

    from function.time_wrap import time_wrap
    t = time.time()
    q = QubeDatabase()
    q.Bj_subjob_count()
    q.get_user_job_name('chenqx')

    # for i in q.view():
    #     print i
    # for i in q.describe_job():
    #     print i
    # print len(q.host())
    # print time.time() - t
    # # print 119028 >> 7
    # # cursor.execute("use 929qube")
    # # cursor.execute("show tables")
    # # cursor.execute("select * from 929qube.119028work")
    # # for i in cursor.fetchall():
    # #     print i
    # #
    # # cursor.execute("DESC 842qube.107900callback")
    # # for i, k in enumerate(cursor.fetchall()):
    # #     print i, k
    #
    # # conn.close()
